


using System;
using System.Data;
using System.Linq;
using System.Linq.Expressions;
using SubSonic.DataProviders;
using SubSonic.Extensions;
using SubSonic.Linq.Structure;
using SubSonic.Query;
using SubSonic.Schema;
using System.Data.Common;
using System.Collections.Generic;

namespace Eacademy.Domain.Entities
{
	public partial class EacademyDB : IQuerySurface
	{

		public IDataProvider DataProvider;
		public DbQueryProvider provider;
		
		public static IDataProvider DefaultDataProvider { get; set; }

		public bool TestMode
		{
			get
			{
				return DataProvider.ConnectionString.Equals("test", StringComparison.InvariantCultureIgnoreCase);
			}
		}

		public EacademyDB() 
		{
			if (DefaultDataProvider == null) {
				DataProvider = ProviderFactory.GetProvider("mysqlConnectionString");
			}
			else {
				DataProvider = DefaultDataProvider;
			}
			Init();
		}

		public EacademyDB(string connectionStringName)
		{
			DataProvider = ProviderFactory.GetProvider(connectionStringName);
			Init();
		}

		public EacademyDB(string connectionString, string providerName)
		{
			DataProvider = ProviderFactory.GetProvider(connectionString,providerName);
			Init();
		}

		public ITable FindByPrimaryKey(string pkName)
		{
			return DataProvider.Schema.Tables.SingleOrDefault(x => x.PrimaryKey.Name.Equals(pkName, StringComparison.InvariantCultureIgnoreCase));
		}

		public Query<T> GetQuery<T>()
		{
			return new Query<T>(provider);
		}
		
		public ITable FindTable(string tableName)
		{
			return DataProvider.FindTable(tableName);
		}
			   
		public IDataProvider Provider
		{
			get { return DataProvider; }
			set {DataProvider=value;}
		}
		
		public DbQueryProvider QueryProvider
		{
			get { return provider; }
		}
		
		BatchQuery _batch = null;
		public void Queue<T>(IQueryable<T> qry)
		{
			if (_batch == null)
				_batch = new BatchQuery(Provider, QueryProvider);
			_batch.Queue(qry);
		}

		public void Queue(ISqlQuery qry)
		{
			if (_batch == null)
				_batch = new BatchQuery(Provider, QueryProvider);
			_batch.Queue(qry);
		}

		public void ExecuteTransaction(IList<DbCommand> commands)
		{
			if(!TestMode)
			{
				using(var connection = commands[0].Connection)
				{
				   if (connection.State == ConnectionState.Closed)
						connection.Open();
				   
				   using (var trans = connection.BeginTransaction()) 
				   {
						foreach (var cmd in commands) 
						{
							cmd.Transaction = trans;
							cmd.Connection = connection;
							cmd.ExecuteNonQuery();
						}
						trans.Commit();
					}
					connection.Close();
				}
			}
		}

		public IDataReader ExecuteBatch()
		{
			if (_batch == null)
				throw new InvalidOperationException("There's nothing in the queue");
			if(!TestMode)
				return _batch.ExecuteReader();
			return null;
		}
			
		public Query<AcountPermissionBase> AcountPermissionBases { get; set; }
		public Query<AssessmentBase> AssessmentBases { get; set; }
		public Query<AssessmentQuestionBase> AssessmentQuestionBases { get; set; }
		public Query<AssessmentRatingBase> AssessmentRatingBases { get; set; }
		public Query<AssessmentStudentBase> AssessmentStudentBases { get; set; }
		public Query<AssignmentBase> AssignmentBases { get; set; }
		public Query<AssignmentsSubjectBase> AssignmentsSubjectBases { get; set; }
		public Query<ChoiceBase> ChoiceBases { get; set; }
		public Query<CometchatBase> CometchatBases { get; set; }
		public Query<CometchatAnnouncementBase> CometchatAnnouncementBases { get; set; }
		public Query<CometchatChatroommessageBase> CometchatChatroommessageBases { get; set; }
		public Query<CometchatChatroomBase> CometchatChatroomBases { get; set; }
		public Query<CometchatChatroomsUserBase> CometchatChatroomsUserBases { get; set; }
		public Query<CometchatMessagesOldBase> CometchatMessagesOldBases { get; set; }
		public Query<CometchatStatusBase> CometchatStatusBases { get; set; }
		public Query<CometchatVideochatsessionBase> CometchatVideochatsessionBases { get; set; }
		public Query<ElmahErrorBase> ElmahErrorBases { get; set; }
		public Query<FriendBase> FriendBases { get; set; }
		public Query<GlossaryBase> GlossaryBases { get; set; }
		public Query<LessonBase> LessonBases { get; set; }
		public Query<LessonsSubjectBase> LessonsSubjectBases { get; set; }
		public Query<MentorBase> MentorBases { get; set; }
		public Query<MentorsSubjectBase> MentorsSubjectBases { get; set; }
		public Query<MessagesConversationBase> MessagesConversationBases { get; set; }
		public Query<MessagesMessageBase> MessagesMessageBases { get; set; }
		public Query<MessagesRecipientBase> MessagesRecipientBases { get; set; }
		public Query<QuestionTypeBase> QuestionTypeBases { get; set; }
		public Query<QuestionBase> QuestionBases { get; set; }
		public Query<QuestionsAnswerBase> QuestionsAnswerBases { get; set; }
		public Query<QuizBase> QuizBases { get; set; }
		public Query<QuizStudentBase> QuizStudentBases { get; set; }
		public Query<QuizSubjectBase> QuizSubjectBases { get; set; }
		public Query<SectionBase> SectionBases { get; set; }
		public Query<SectionsSubjectBase> SectionsSubjectBases { get; set; }
		public Query<StudentBase> StudentBases { get; set; }
		public Query<SubjectBase> SubjectBases { get; set; }
		public Query<SubjectsEnrolledBase> SubjectsEnrolledBases { get; set; }
		public Query<UserBase> UserBases { get; set; }
		public Query<UsersPermissionBase> UsersPermissionBases { get; set; }
		public Query<UsersTypeBase> UsersTypeBases { get; set; }

			

		#region ' Aggregates and SubSonic Queries '
		public Select SelectColumns(params string[] columns)
		{
			return new Select(DataProvider, columns);
		}

		public Select Select
		{
			get { return new Select(this.Provider); }
		}

		public Insert Insert
		{
			get { return new Insert(this.Provider); }
		}

		public Update<T> Update<T>() where T:new()
		{
			return new Update<T>(this.Provider);
		}

		public SqlQuery Delete<T>(Expression<Func<T,bool>> column) where T:new()
		{
			LambdaExpression lamda = column;
			SqlQuery result = new Delete<T>(this.Provider);
			result = result.From<T>();
			result.Constraints=lamda.ParseConstraints().ToList();
			return result;
		}

		public SqlQuery Max<T>(Expression<Func<T,object>> column)
		{
			LambdaExpression lamda = column;
			string colName = lamda.ParseObjectValue();
			string objectName = typeof(T).Name;
			string tableName = DataProvider.FindTable(objectName).Name;
			return new Select(DataProvider, new Aggregate(colName, AggregateFunction.Max)).From(tableName);
		}

		public SqlQuery Min<T>(Expression<Func<T,object>> column)
		{
			LambdaExpression lamda = column;
			string colName = lamda.ParseObjectValue();
			string objectName = typeof(T).Name;
			string tableName = this.Provider.FindTable(objectName).Name;
			return new Select(this.Provider, new Aggregate(colName, AggregateFunction.Min)).From(tableName);
		}

		public SqlQuery Sum<T>(Expression<Func<T,object>> column)
		{
			LambdaExpression lamda = column;
			string colName = lamda.ParseObjectValue();
			string objectName = typeof(T).Name;
			string tableName = this.Provider.FindTable(objectName).Name;
			return new Select(this.Provider, new Aggregate(colName, AggregateFunction.Sum)).From(tableName);
		}

		public SqlQuery Avg<T>(Expression<Func<T,object>> column)
		{
			LambdaExpression lamda = column;
			string colName = lamda.ParseObjectValue();
			string objectName = typeof(T).Name;
			string tableName = this.Provider.FindTable(objectName).Name;
			return new Select(this.Provider, new Aggregate(colName, AggregateFunction.Avg)).From(tableName);
		}

		public SqlQuery Count<T>(Expression<Func<T,object>> column)
		{
			LambdaExpression lamda = column;
			string colName = lamda.ParseObjectValue();
			string objectName = typeof(T).Name;
			string tableName = this.Provider.FindTable(objectName).Name;
			return new Select(this.Provider, new Aggregate(colName, AggregateFunction.Count)).From(tableName);
		}

		public SqlQuery Variance<T>(Expression<Func<T,object>> column)
		{
			LambdaExpression lamda = column;
			string colName = lamda.ParseObjectValue();
			string objectName = typeof(T).Name;
			string tableName = this.Provider.FindTable(objectName).Name;
			return new Select(this.Provider, new Aggregate(colName, AggregateFunction.Var)).From(tableName);
		}

		public SqlQuery StandardDeviation<T>(Expression<Func<T,object>> column)
		{
			LambdaExpression lamda = column;
			string colName = lamda.ParseObjectValue();
			string objectName = typeof(T).Name;
			string tableName = this.Provider.FindTable(objectName).Name;
			return new Select(this.Provider, new Aggregate(colName, AggregateFunction.StDev)).From(tableName);
		}

		#endregion

		void Init()
		{
			provider = new DbQueryProvider(this.Provider);

			#region ' Query Defs '
			AcountPermissionBases = new Query<AcountPermissionBase>(provider);
			AssessmentBases = new Query<AssessmentBase>(provider);
			AssessmentQuestionBases = new Query<AssessmentQuestionBase>(provider);
			AssessmentRatingBases = new Query<AssessmentRatingBase>(provider);
			AssessmentStudentBases = new Query<AssessmentStudentBase>(provider);
			AssignmentBases = new Query<AssignmentBase>(provider);
			AssignmentsSubjectBases = new Query<AssignmentsSubjectBase>(provider);
			ChoiceBases = new Query<ChoiceBase>(provider);
			CometchatBases = new Query<CometchatBase>(provider);
			CometchatAnnouncementBases = new Query<CometchatAnnouncementBase>(provider);
			CometchatChatroommessageBases = new Query<CometchatChatroommessageBase>(provider);
			CometchatChatroomBases = new Query<CometchatChatroomBase>(provider);
			CometchatChatroomsUserBases = new Query<CometchatChatroomsUserBase>(provider);
			CometchatMessagesOldBases = new Query<CometchatMessagesOldBase>(provider);
			CometchatStatusBases = new Query<CometchatStatusBase>(provider);
			CometchatVideochatsessionBases = new Query<CometchatVideochatsessionBase>(provider);
			ElmahErrorBases = new Query<ElmahErrorBase>(provider);
			FriendBases = new Query<FriendBase>(provider);
			GlossaryBases = new Query<GlossaryBase>(provider);
			LessonBases = new Query<LessonBase>(provider);
			LessonsSubjectBases = new Query<LessonsSubjectBase>(provider);
			MentorBases = new Query<MentorBase>(provider);
			MentorsSubjectBases = new Query<MentorsSubjectBase>(provider);
			MessagesConversationBases = new Query<MessagesConversationBase>(provider);
			MessagesMessageBases = new Query<MessagesMessageBase>(provider);
			MessagesRecipientBases = new Query<MessagesRecipientBase>(provider);
			QuestionTypeBases = new Query<QuestionTypeBase>(provider);
			QuestionBases = new Query<QuestionBase>(provider);
			QuestionsAnswerBases = new Query<QuestionsAnswerBase>(provider);
			QuizBases = new Query<QuizBase>(provider);
			QuizStudentBases = new Query<QuizStudentBase>(provider);
			QuizSubjectBases = new Query<QuizSubjectBase>(provider);
			SectionBases = new Query<SectionBase>(provider);
			SectionsSubjectBases = new Query<SectionsSubjectBase>(provider);
			StudentBases = new Query<StudentBase>(provider);
			SubjectBases = new Query<SubjectBase>(provider);
			SubjectsEnrolledBases = new Query<SubjectsEnrolledBase>(provider);
			UserBases = new Query<UserBase>(provider);
			UsersPermissionBases = new Query<UsersPermissionBase>(provider);
			UsersTypeBases = new Query<UsersTypeBase>(provider);
			#endregion


			#region ' Schemas '
			if(DataProvider.Schema.Tables.Count == 0)
			{
				DataProvider.Schema.Tables.Add(new AcountPermissionTable(DataProvider));
				DataProvider.Schema.Tables.Add(new AssessmentTable(DataProvider));
				DataProvider.Schema.Tables.Add(new AssessmentQuestionsTable(DataProvider));
				DataProvider.Schema.Tables.Add(new AssessmentRatingsTable(DataProvider));
				DataProvider.Schema.Tables.Add(new AssessmentStudentTable(DataProvider));
				DataProvider.Schema.Tables.Add(new AssignmentsTable(DataProvider));
				DataProvider.Schema.Tables.Add(new AssignmentsSubjectsTable(DataProvider));
				DataProvider.Schema.Tables.Add(new ChoicesTable(DataProvider));
				DataProvider.Schema.Tables.Add(new CometchatTable(DataProvider));
				DataProvider.Schema.Tables.Add(new CometchatAnnouncementsTable(DataProvider));
				DataProvider.Schema.Tables.Add(new CometchatChatroommessagesTable(DataProvider));
				DataProvider.Schema.Tables.Add(new CometchatChatroomsTable(DataProvider));
				DataProvider.Schema.Tables.Add(new CometchatChatroomsUsersTable(DataProvider));
				DataProvider.Schema.Tables.Add(new CometchatMessagesOldTable(DataProvider));
				DataProvider.Schema.Tables.Add(new CometchatStatusTable(DataProvider));
				DataProvider.Schema.Tables.Add(new CometchatVideochatsessionsTable(DataProvider));
				DataProvider.Schema.Tables.Add(new ElmahErrorTable(DataProvider));
				DataProvider.Schema.Tables.Add(new FriendsTable(DataProvider));
				DataProvider.Schema.Tables.Add(new GlossaryTable(DataProvider));
				DataProvider.Schema.Tables.Add(new LessonsTable(DataProvider));
				DataProvider.Schema.Tables.Add(new LessonsSubjectsTable(DataProvider));
				DataProvider.Schema.Tables.Add(new MentorsTable(DataProvider));
				DataProvider.Schema.Tables.Add(new MentorsSubjectsTable(DataProvider));
				DataProvider.Schema.Tables.Add(new MessagesConversationsTable(DataProvider));
				DataProvider.Schema.Tables.Add(new MessagesMessagesTable(DataProvider));
				DataProvider.Schema.Tables.Add(new MessagesRecipientsTable(DataProvider));
				DataProvider.Schema.Tables.Add(new QuestionTypesTable(DataProvider));
				DataProvider.Schema.Tables.Add(new QuestionsTable(DataProvider));
				DataProvider.Schema.Tables.Add(new QuestionsAnswersTable(DataProvider));
				DataProvider.Schema.Tables.Add(new QuizTable(DataProvider));
				DataProvider.Schema.Tables.Add(new QuizStudentsTable(DataProvider));
				DataProvider.Schema.Tables.Add(new QuizSubjectsTable(DataProvider));
				DataProvider.Schema.Tables.Add(new SectionsTable(DataProvider));
				DataProvider.Schema.Tables.Add(new SectionsSubjectsTable(DataProvider));
				DataProvider.Schema.Tables.Add(new StudentsTable(DataProvider));
				DataProvider.Schema.Tables.Add(new SubjectsTable(DataProvider));
				DataProvider.Schema.Tables.Add(new SubjectsEnrolledTable(DataProvider));
				DataProvider.Schema.Tables.Add(new UsersTable(DataProvider));
				DataProvider.Schema.Tables.Add(new UsersPermissionTable(DataProvider));
				DataProvider.Schema.Tables.Add(new UsersTypeTable(DataProvider));
			}
			#endregion
		}
		

		#region ' Helpers '
			
		internal static DateTime DateTimeNowTruncatedDownToSecond() {
			var now = DateTime.Now;
			return now.AddTicks(-now.Ticks % TimeSpan.TicksPerSecond);
		}

		#endregion

	}
}